
// Description: This code makes a single master dataset which contains Florida inpt data from 2007-2017. This master dataset contains insurance type, indicator variables for different types of procedures, and basic information such as the year.

*** Inpatient Discharge Records

//************************* 2008-2011 Under ICD9 *******************************

clear

cd "~/Documents/Research/data/AHCA/INP"	

forval i = 7/11 {								

	forval j = 1/4 {
	
		if (`i'==7 & `j'==1) | (`i'==7 & `j'==2) | (`i'==7 & `j'==3) | (`i'==11 & `j'==4) {
			display "Inp`i'Q`j' does not exist"
			}

		else{
			tempfile tfile_`i'_`j'
			use Inp`i'Q`j' , clear
			
			rename * , lower

			capture gen qtrreal = real(qtr)
			capture gen qtrreal = qtr			// later years use numeric entries for quarter //
			
			capture gen race_real = real(race)
			capture gen race_real = race
			
			capture gen fac_county_r = real(fac_county)
			capture gen fac_county_r = fac_county
			
			egen totalDiags = rownonmiss(othdiag1-othdiag30), strok
			replace totalDiags = totalDiags+1 if prindiag!=""
			
			gen time = yq(year, qtrreal)
			
			if (`i'==7 | `i'==8 | `i'==9) {
				rename gender sex
			}

			keep year qtrreal time faclnbr age sex race_real totalDiags payer prindiag 				   othdiag1-othdiag30 prinproc othproc1-othproc30 fac_county_r losdays
				
			save "`tfile_`i'_`j''"
			}	
		}
	}


* Assemble Analytic Data		
		
use "`tfile_7_4'" , clear

forval r = 8/10 {
	forval s = 1/4 {
		append using "`tfile_`r'_`s''"
		}
	}

forval q = 1/3 {
	append using "`tfile_11_`q''"
	}

compress
save "~/Desktop/FL_unclean_inpt_mini_08to11", replace

//************************* 2012-2015 Under ICD9 *******************************

clear

forval i = 11/15 {								

	forval j = 1/4 {
	
		if (`i'==11 & `j'==1) | (`i'==11 & `j'==2) | (`i'==11 & `j'==3) | (`i'==15 & `j'==4) {
			display "Inp`i'Q`j' does not exist"
			}

		else{
			tempfile tfile_`i'_`j'
			use Inp`i'Q`j' , clear
			
			rename * , lower

			capture gen qtrreal = real(qtr)
			capture gen qtrreal = qtr			// later years use numeric entries for quarter //
			
			capture gen race_real = real(race)
			capture gen race_real = race
			
			capture gen fac_county_r = real(fac_county)
			capture gen fac_county_r = fac_county
			
			egen totalDiags = rownonmiss(othdiag1-othdiag30), strok
			replace totalDiags = totalDiags+1 if prindiag!=""
			
			gen time = yq(year, qtrreal)
			
			if (`i'==7 | `i'==8 | `i'==9) {
				rename gender sex
			}

			keep year qtrreal time faclnbr age sex race_real totalDiags payer prindiag 				   othdiag1-othdiag30 prinproc othproc1-othproc30 fac_county_r losdays
				
			save "`tfile_`i'_`j''"
			}	
		}
	}


* Assemble Analytic Data		
		
use "`tfile_11_4'" , clear

forval r = 12/14 {
	forval s = 1/4 {
		append using "`tfile_`r'_`s''"
		}
	}

forval q = 1/3 {
	append using "`tfile_15_`q''"
	}

compress
save "~/Desktop/FL_unclean_inpt_mini_12to15", replace

//************************* 2016-2017 Under ICD10 *******************************

clear

forval i = 15/17 {								

	forval j = 1/4 {
	
		if (`i'==15 & `j'==1) | (`i'==15 & `j'==2) | (`i'==15 & `j'==3) | (`i'==17 & `j'==4) {
			display "Inp`i'Q`j' does not exist"
			}

		else{
			tempfile tfile_`i'_`j'
			use Inp`i'Q`j' , clear
			
			rename * , lower

			capture gen qtrreal = real(qtr)
			capture gen qtrreal = qtr			// later years use numeric entries for quarter //
			
			capture gen race_real = real(race)
			capture gen race_real = race
			
			capture gen fac_county_r = real(fac_county)
			capture gen fac_county_r = fac_county
			
			gen time = yq(year, qtrreal)
			
			if (`i'==7 | `i'==8 | `i'==9) {
				rename gender sex
			}

			keep year qtrreal time faclnbr age sex race_real payer prindiag othdiag1-				  othdiag30 prinproc othproc1-othproc30 fac_county_r losdays
				
			save "`tfile_`i'_`j''"
			}	
		}
	}


* Assemble Analytic Data		
		
use "`tfile_15_4'" , clear

forval s = 1/4 {
	append using "`tfile_16_`s''"
	}

forval q = 1/3 {
	append using "`tfile_17_`q''"
	}

compress
save "~/Desktop/FL_unclean_inpt_mini_16to17", replace

//********************************************************************************
//**********************Work with 2008-2011 data under ICD9***********************
//********************************************************************************

*** Make Outcome Variables

clear

cd "~/Desktop"

use FL_unclean_inpt_mini_08to11.dta, clear

tab year

// Create white variable (different coding in different years)

gen white=.
 
replace white=0 if ((race_real>=1 & race_real<=3) | race_real==6 | race_real==7) & (year>=2006 & year<=2009)
replace white=1 if (race_real==4 | race_real==5) & (year>=2006 & year<=2009)
  
replace white=0 if ((race_real>=1 & race_real<=4) | race_real==6) & (year>=2010 & year<=2015)
replace white=1 if (race_real==5) & (year==2010 | year==2011)

drop race_real

// Convert year to fiscal year

gen     part =.
replace part =1 if (qtrreal==1 | qtrreal==2 | qtrreal==3)
replace part =2 if qtrreal==4

gen FisYear=.
replace FisYear=2008 if (year==2007 & part==2) | (year==2008 & part==1)
replace FisYear=2009 if (year==2008 & part==2) | (year==2009 & part==1)
replace FisYear=2010 if (year==2009 & part==2) | (year==2010 & part==1)
replace FisYear=2011 if (year==2010 & part==2) | (year==2011 & part==1)
replace FisYear=2012 if (year==2011 & part==2) | (year==2012 & part==1)
replace FisYear=2013 if (year==2012 & part==2) | (year==2013 & part==1)
replace FisYear=2014 if (year==2013 & part==2) | (year==2014 & part==1)
replace FisYear=2015 if (year==2014 & part==2) | (year==2015 & part==1)

tab FisYear

drop year part

// Change variable names to match NC data
rename faclnbr facility_id
rename FisYear year
rename age agey

// Simplify insurance types

gen medicaid = (payer=="C" | payer=="D")

gen private  = (payer=="E" | payer=="F" | payer=="G")
	
gen otherIns = (payer=="A" | payer=="B" | payer=="H" | payer=="I" | payer=="J" | payer=="K" | payer=="L" | payer=="M" | payer=="N" | payer=="O")

drop payer

// Call prinical diagnosis othdiag0 for easier coding
rename prindiag othdiag0

// Call prinical procedure othproc0 for easier coding
rename prinproc othproc0

// Total procedure codes
egen procedureCount = rownonmiss(othproc0-othproc30), strok
			
// Removing dx preceded by "E" or "V" since these are not of interest
					
forval n = 0/30 {
				
	gen firstChar`n' = substr(othdiag`n',1,1)
					
	}
							
forval n = 0/30 {
				
	replace othdiag`n'="" if firstChar`n'=="E" | firstChar`n'=="V"
					
	}
	
drop firstChar1-firstChar30
			
// Make dx and procedure codes numeric
					
forval n = 0/30 {
				
	destring othdiag`n', gen (othdiag`n'_r)
					
	}

forval n = 0/30 {
				
	destring othproc`n', gen (othproc`n'_r)
					
	}

// Hypertension complicating pregnancy childbirth and the puerperium using ICD9 codes
	
gen compHypertension=0
	
forval n = 0/30 {
			
	replace compHypertension = 1 if inrange(othdiag`n'_r, 642, 642.94)
				
	}
		
// Overweight and Obesity using ICD9 codes
	
gen overweightObese=0

forval n = 0/30 {
			
	replace overweightObese = 1 if inrange(othdiag`n'_r, 278.0, 278.03)
				
	}
			
// Diabetes - Type I, Type II, Diabetes complicating pregnancy, Abnormal glucose complicating pregnancy, Abnormal glucose, secondary diabetes
	
gen diabetes=0
	
forval n = 0/30 {
			
	replace diabetes = 1 if (inrange(othdiag`n'_r, 250, 250.93) | inrange(othdiag`n'_r, 648, 648.04) | inrange(othdiag`n'_r, 648.8, 648.84) | inrange(othdiag`n'_r, 790.2, 790.29) | inrange(othdiag`n'_r, 249, 249.91))
				
	}
	
// Proc codes for C-Section, Forceps or Vacuum, Medical Induction, Other
//                C-Section = c_section
//                Forceps or Vacuum = code72
//                Medical Induction = drugInduce
//                Other Obstetric Proc = otherObProc
	
// A variable for all deliveries will also be needed = allDeliv
// This variable will consist of: c_section, vagNoProc, vagProc
	
gen c_section = 0
gen code72 = 0
gen drugInduce = 0
gen otherObProc = 0
	
gen vagNoProc= 0
gen vagProc = 0
	
forval m = 0/30 {
		
	replace c_section = 1 if (inrange(othproc`m'_r, 74, 74.99) & othproc`m'_r!=74.3 & othproc`m'_r!=74.91)
			
	replace code72 = 1 if (inrange(othproc`m'_r, 72, 72.9) | othproc`m'_r==73.21 | othproc`m'_r==73.91 | othproc`m'_r==73.92) & othproc`m'_r!=72.8 & othproc`m'_r!=72.9
			
	replace drugInduce = 1 if othproc`m'_r==73.4
			
	replace otherObProc = 1 if (inrange(othproc`m'_r, 73, 73.99) | othproc`m'_r==72.8 | othproc`m'_r==72.9) & othproc`m'_r!=73.21 & othproc`m'_r!=73.91 & othproc`m'_r!=73.92 & othproc`m'_r!=73.51 & othproc`m'_r!=73.59
			
			
			
	replace vagNoProc = 1 if inrange(othproc`m'_r, 73.51, 73.59)
		
	replace vagProc = 1 if (inrange(othproc`m'_r, 72, 73.99) & othproc`m'_r!=73.51 & othproc`m'_r!=73.59)
		
	}
	
// Create state varible

gen state="FL"

// Only keep necessary variables

keep year state facility_id white medicaid private otherIns compHypertension overweightObese diabetes c_section code72 drugInduce otherObProc vagNoProc vagProc agey procedureCount losdays

// Save dataset

save "~/Desktop/FL_inpt_mini_08to11", replace

//********************************************************************************
//**********************Work with 2012-2015 data under ICD9***********************
//********************************************************************************

*** Make Outcome Variables

clear

use FL_unclean_inpt_mini_12to15.dta, clear

tab year

// Convert year to fiscal year

gen     part =.
replace part =1 if (qtrreal==1 | qtrreal==2 | qtrreal==3)
replace part =2 if qtrreal==4

gen FisYear=.
replace FisYear=2008 if (year==2007 & part==2) | (year==2008 & part==1)
replace FisYear=2009 if (year==2008 & part==2) | (year==2009 & part==1)
replace FisYear=2010 if (year==2009 & part==2) | (year==2010 & part==1)
replace FisYear=2011 if (year==2010 & part==2) | (year==2011 & part==1)
replace FisYear=2012 if (year==2011 & part==2) | (year==2012 & part==1)
replace FisYear=2013 if (year==2012 & part==2) | (year==2013 & part==1)
replace FisYear=2014 if (year==2013 & part==2) | (year==2014 & part==1)
replace FisYear=2015 if (year==2014 & part==2) | (year==2015 & part==1)

tab FisYear

drop year part

// Change variable names to match NC data
rename faclnbr facility_id
rename FisYear year
rename age agey

// Create white variable
gen white=.
replace white=0 if (race_real>=1 & race_real<=4) | race_real==6
replace white=1 if race_real==5

drop race_real

// Simplify insurance types

gen medicaid = (payer=="C" | payer=="D")

gen private  = (payer=="E" | payer=="F" | payer=="G")
	
gen otherIns = (payer=="A" | payer=="B" | payer=="H" | payer=="I" | payer=="J" | payer=="K" | payer=="L" | payer=="M" | payer=="N" | payer=="O")

drop payer

// Call prinical diagnosis othdiag0 for easier coding
rename prindiag othdiag0

// Call prinical procedure othproc0 for easier coding
rename prinproc othproc0

// Total procedure codes
egen procedureCount = rownonmiss(othproc0-othproc30), strok
			
// Removing dx preceded by "E" or "V" since these are not of interest
		
forval n = 0/30 {
				
	gen firstChar`n' = substr(othdiag`n',1,1)
					
	}
						
forval n = 0/30 {
				
	replace othdiag`n'="" if firstChar`n'=="E" | firstChar`n'=="V"
					
	}
	
drop firstChar0-firstChar30

// Make dx and procedure codes numeric
					
forval n = 0/30 {
				
	destring othdiag`n', gen (othdiag`n'_r)
					
	}

forval n = 0/30 {
				
	destring othproc`n', gen (othproc`n'_r)
					
	}

// Hypertension complicating pregnancy childbirth and the puerperium using ICD9 codes
	
gen compHypertension=0

forval n = 0/30 {
			
	replace compHypertension = 1 if inrange(othdiag`n'_r, 642, 642.94)
				
	}
	
// Overweight and Obesity using ICD9 codes
	
gen overweightObese=0
	
forval n = 0/30 {
			
	replace overweightObese = 1 if inrange(othdiag`n'_r, 278.0, 278.03)
				
	}
	
// Diabetes - Type I, Type II, Diabetes complicating pregnancy, Abnormal glucose complicating pregnancy, Abnormal glucose, secondary diabetes
	
gen diabetes=0
	
forval n = 0/30 {
			
	replace diabetes = 1 if (inrange(othdiag`n'_r, 250, 250.93) | inrange(othdiag`n'_r, 648, 648.04) | inrange(othdiag`n'_r, 648.8, 648.84) | inrange(othdiag`n'_r, 790.2, 790.29) | inrange(othdiag`n'_r, 249, 249.91))
				
	}
	
// Proc codes for C-Section, Forceps or Vacuum, Medical Induction, Other
//                C-Section = c_section
//                Forceps or Vacuum = code72
//                Medical Induction = drugInduce
//                Other Obstetric Proc = otherObProc
	
// A variable for all deliveries will also be needed = allDeliv
// This variable will consist of: c_section, vagNoProc, vagProc
	
gen c_section = 0
gen code72 = 0
gen drugInduce = 0
gen otherObProc = 0
	
gen vagNoProc= 0
gen vagProc = 0
	
forval m = 0/30 {
		
	replace c_section = 1 if (inrange(othproc`m'_r, 74, 74.99) & othproc`m'_r!=74.3 & othproc`m'_r!=74.91)
			
	replace code72 = 1 if (inrange(othproc`m'_r, 72, 72.9) | othproc`m'_r==73.21 | othproc`m'_r==73.91 | othproc`m'_r==73.92) & othproc`m'_r!=72.8 & othproc`m'_r!=72.9
			
	replace drugInduce = 1 if othproc`m'_r==73.4
			
	replace otherObProc = 1 if (inrange(othproc`m'_r, 73, 73.99) | othproc`m'_r==72.8 | othproc`m'_r==72.9) & othproc`m'_r!=73.21 & othproc`m'_r!=73.91 & othproc`m'_r!=73.92 & othproc`m'_r!=73.51 & othproc`m'_r!=73.59
			
			
			
	replace vagNoProc = 1 if inrange(othproc`m'_r, 73.51, 73.59)
		
	replace vagProc = 1 if (inrange(othproc`m'_r, 72, 73.99) & othproc`m'_r!=73.51 & othproc`m'_r!=73.59)
		
	}
	
// Create state varible

gen state="FL"

// Only keep necessary variables

keep year state facility_id white medicaid private otherIns compHypertension overweightObese diabetes c_section code72 drugInduce otherObProc vagNoProc vagProc agey procedureCount losdays

// Save dataset

save "~/Desktop/FL_inpt_mini_12to15", replace
	
	
//********************************************************************************
//********************* Work with 2016-2017 data under ICD10 *********************
//********************************************************************************


*** Make Outcome Variables

clear

use FL_unclean_inpt_mini_16to17, clear

tab year

// Convert year to fiscal year

gen     part =.
replace part =1 if (qtrreal==1 | qtrreal==2 | qtrreal==3)
replace part =2 if qtrreal==4

gen FisYear=.
replace FisYear=2016 if (year==2015 & part==2) | (year==2016 & part==1)
replace FisYear=2017 if (year==2016 & part==2) | (year==2017 & part==1)

tab FisYear

// Change variable names to match NC data

rename faclnbr facility_id
drop year
rename FisYear year
rename age agey

// Create white variable

gen white=.
replace white=0 if (race_real>=1 & race_real<=4) | race_real==6
replace white=1 if race_real==5

// Simplify insurance types

gen medicaid = (payer=="C" | payer=="D")

gen private  = (payer=="E" | payer=="F" | payer=="G")
	
gen otherIns = (payer=="A" | payer=="B" | payer=="H" | payer=="I" | payer=="J" | payer=="K" | payer=="L" | payer=="M" | payer=="N" | payer=="O")

// Call prinical diagnosis othdiag0 for easier coding
rename prindiag othdiag0

// Call prinical procedure othproc0 for easier coding
rename prinproc othproc0

// Total procedure codes
egen procedureCount = rownonmiss(othproc0-othproc30), strok

// Grab first one of ICD-10

forval m = 0/30 {
		
	gen firstOne`m' = substr(othdiag`m',1,1)
		
}

// Grab 2nd and 3rd numbers of ICD 10

forval m = 0/30 {
		
	gen firstNum`m' = substr(othdiag`m',2,2)
		
}

forval m = 0/30 {
		
	gen long firstNum`m'_r = real(firstNum`m')
		
}

// Grab first three of ICD-10
	
forval m = 0/30 {
		
	gen firstThree`m' = substr(othdiag`m',1,3)
		
}
	
// Number with decimal following the letter - ICD10
	
//------- Get Length
	
forval m = 0/30 {
		
	gen strLength`m' = strlen(othdiag`m')
		
}
	
//-------- Remove first character
	
forval m = 0/30 {
		
	gen numOnly`m' = substr(othdiag`m',2,strLength`m'-1)
		
}
	
//--------- Convert to numeric - already has decimal
//--------- Note: ICD10 codes with letters at the end no longer appear
	
forval m = 0/30 {
			
	gen ICD10dec`m'_r =real(numOnly`m') 
			
	}

// Hypertension complicating pregnancy childbirth and the puerperium using ICD10 codes
	
gen compHypertension=0

forval n = 0/30 {
			
	replace compHypertension = 1 if firstThree`n'=="O10" | firstThree`n'=="O11" | firstThree`n'=="O13" | firstThree`n'=="O14" | firstThree`n'=="O15" | firstThree`n'=="O16"
				
	}	
		
// Overweight and Obesity using ICD10 codes
	
gen overweightObese=0

forval n = 0/30 {
			
	replace overweightObese = 1 if firstThree`n'=="E66"
				
	}
		
// Diabetes - Type I, Type II, Diabetes complicating pregnancy, Abnormal glucose complicating pregnancy, Abnormal glucose, secondary diabetes
	
gen diabetes=0
		
forval n = 0/30 {
		
	replace diabetes = 1 if ( firstThree`n'=="E08" | firstThree`n'=="E09" | firstThree`n'=="E10" | firstThree`n'=="E11" | firstThree`n'=="E13" | firstThree`n'=="O24" | firstThree`n'=="R73" | (firstOne`n'=="O" & inrange(ICD10dec`n'_r, 99.81, 99.815)) )
		
}
	
// Procedure first three characters

forval k = 0/30 {
	
	gen procFstThree`k' = substr(othproc`k',1,3)
		
	}
		
// Procedure code - 7th character

forval k = 0/30 {
	
	gen procLast`k' = substr(othproc`k',7,1)
	gen procLast`k'_r = real(procLast`k')
		
	}
	
// Proc codes for C-Section, Forceps or Vacuum, Medical Induction, Other
//                C-Section = c_section
//                Forceps or Vacuum = code72
//                Medical Induction = drugInduce
//                Other Obstetric Proc = otherObProc
	
// A variable for all deliveries will also be needed = allDeliv
// This variable will consist of: c_section, vagNoProc, vagProc
	
gen c_section = 0
gen code72 = 0
gen drugInduce = 0
gen otherObProc = 0
	
gen vagNoProc= 0
gen vagProc = 0
		
forval m = 0/30 {
	
	replace c_section = 1 if procFstThree`m'=="10D" & inrange(procLast`m'_r, 0, 2)
	
	replace vagNoProc = 1 if procFstThree`m'=="10E"
	
	}
	
cd "~/Documents/Research/data/other"
	
// ICD10 for code72
	
forval n = 0/30 {
		
	rename othproc`n' Code72
	merge m:1 Code72 using ICD10_ForcepsVacuum.dta, gen(Code72_`n')
	rename Code72 othproc`n' 
			
	}	
		
forval n = 0/30 {
		
	replace code72 = 1 if Code72_`n'==3
	drop if Code72_`n'==2
		
	}
	
//ICD10 for drugInduce
	
forval n = 0/30 {
		
	rename othproc`n' drugInduceVar
	merge m:1 drugInduceVar using ICD10_MedicalInduction.dta, gen(drugInduceVar`n')
	rename drugInduceVar othproc`n' 
			
	}	
		
forval n = 0/30 {
		
	replace drugInduce = 1 if drugInduceVar`n'==3
	drop if drugInduceVar`n'==2
		
	}
	
//ICD10 for otherObProc
	
forval n = 0/30 {
		
	rename othproc`n' otherObProcVar
	merge m:1 otherObProcVar using ICD10_OtherObProc.dta, gen(otherObProcVar`n')
	rename otherObProcVar othproc`n' 
			
	}	
		
forval n = 0/30 {
		
	replace otherObProc = 1 if otherObProcVar`n'==3
	drop if otherObProcVar`n'==2
		
	}
	
//ICD10 for vagProc
	
forval n = 0/30 {
		
	rename othproc`n' ICD10VagProc
	merge m:1 ICD10VagProc using ICD10_CompleteObProc.dta, gen(ICD10VagProc`n')
	rename ICD10VagProc othproc`n'
			
	}	
		
forval n = 0/30 {
		
	replace vagProc = 1 if ICD10VagProc`n'==3 
	drop if ICD10VagProc`n'==2
		
	}

// Create state varible

gen state="FL"

// Only keep necessary variables

keep year state facility_id white medicaid private otherIns compHypertension overweightObese diabetes c_section code72 drugInduce otherObProc vagNoProc vagProc agey procedureCount losdays

// Save dataset

save "~/Desktop/FL_inpt_mini_16to17", replace

//********************************************************************************
//************************* Merge Three FL datasets ******************************
//********************************************************************************

clear

cd "~/Desktop"

use FL_inpt_mini_08to11.dta , clear
append using "FL_inpt_mini_12to15.dta"
append using "FL_inpt_mini_16to17.dta"

// Deal with missing
replace agey=. if agey>=130

// Create all delivery variable
gen allDeliv=0
replace allDeliv=1 if vagNoProc==1 | vagProc==1 | c_section==1

save "~/Desktop/FL_inpt_mini_08to17", replace




